Challenge #3: 移動平均 に挑戦！ – Alteryx Community, Weekly Challenge

こんにちは。Alteryxサポートエンジニアとして勉強中のスズです。

Alteryx Communityには、学習リソースの1つとしてWeekly Challengeがあります。Weekly Challengeのご紹介については、以下のエントリをご参照ください。

• AlteryxのWeekly Challenge紹介｜ Developers.IO

本エントリは、Weekly Challengeに挑戦した記録です。

Weekly Challengeの過去のエントリでは既に模範解答が用意されていますが、解答を参照しない状態で挑戦しています。本エントリにてご紹介するワークフローは、数ある解答のうちの1つとしてご参考にしていただきますと幸いです。

Challenge #3: 移動平均

お題

今回挑戦するお題はこちらです。

• Challenge #3: Running Averages｜Alteryx Community

「Input」側には以下のデータが用意されています。

「Output」側のデータは以下の通りです。「Input」側のデータを処理して、以下のデータを作成します。

「Output」側のデータには3ヶ月と6ヶ月の平均が追加されています。3ヶ月分または6ヶ月分のデータが不足している場合は、古いデータを用いて調整しています。例えば、2009年2月の「u.CAGI」の3ヶ月の平均は、2009年1月「1178948」と2009年2月「1066974」を用いて、「(1178948 + 1178948 + 1066974) / 3 = 1141623.333333」となります。

解答の概要

今回作成するワークフローは以下の通りです。

1. Record IDツールでIDを付与
2. Transposeツールでデータを縦持ちに変換
3. FormulaツールでNullを0に変換
4. Multi-Row Formulaツールで3ヶ月分と6ヶ月分のデータを足して出力
5. Cross Tabツールでデータを横持ちに変換
6. Multi-Field Formulaツールで3ヶ月と6ヶ月の平均を出力
7. Join Multipleツールで3つのデータを結合
8. Browseツールで結果の確認

解答の詳細

Preparation -> Record ID を使用して、新しく「RecordID」列を追加してIDを割り当てます。

Transform -> Transpose を使用して、データを縦持ちに変換します。Key Fields に「RecordID」「Year」「Month」列を選択、Data Fields には「u.CAGI」「d.CAGI」「u.IR」「d.IR」「u.NonIR」「d.NonIR」列を選択します。

これにより、「Value」列にデータが並びました。

いったん In/Out -> Borwse を接続してデータを確認してみると、「Nulls」が「712」となっており、Nullが含まれていることが分かります。

Nullが含まれていると計算に支障があるため、先にNullを0に置き換えます。Preparation -> Formula を使用してNullを0に変換します。

ここからは、3ヶ月の平均を出力するデータと6ヶ月の平均を出力するデータに分けて処理を行います。Preparation -> Multi-Row Formula を使用して、3ヶ月分と6ヶ月分のデータを足して出力します。Create New Field を選択して新しい列を作成、Double型でデータを出力します。Group By (Optional) に「Name」列を指定して、「u.CAGI」「d.CAGI」などでグループ化します。

Expressionには以下の通り設定しました。

• 3ヶ月の合計

if [Year] == 2009 AND [Month] == 1 then [Value] * 3
elseif [Year] == 2009 AND [Month] == 2 then [Row-1:Value] * 2  + [Value]
else [Row-2:Value] + [Row-1:Value] + [Value]
endif

• 6ヶ月の合計

if [Year] == 2009 AND [Month] == 1 then [Value] * 6
elseif [Year] == 2009 AND [Month] == 2 then [Row-1:Value] * 5 + [Value]
elseif [Year] == 2009 AND [Month] == 3 then [Row-2:Value] * 4 + [Row-1:Value] + [Value]
elseif [Year] == 2009 AND [Month] == 4 then [Row-3:Value] * 3 + [Row-2:Value] + [Row-1:Value] + [Value]
elseif [Year] == 2009 AND [Month] == 5 then [Row-4:Value] * 2 + [Row-3:Value] + [Row-2:Value] + [Row-1:Value] + [Value]
else [Row-5:Value] + [Row-4:Value] + [Row-3:Value] + [Row-2:Value] + [Row-1:Value] + [Value]
endif

Transform -> Cross Tab を使用して、3ヶ月分と6ヶ月分のデータを横持ちに変換します。Group Data by these Values に「RecordID」列、New Column Headers に「Name」列、Values for New Columns に3ヶ月分または6ヶ月分のデータ（1つ前のMulti-Row Formulaツールで新しく作成した列）を選択します。

Preparation -> Multi-Field Formula を使用して、3ヶ月と6ヶ月の平均を出力します。Select には「d_CAGI」「d_IR」「d_NonIR」「u_CAGI」「u_IR」「u_NonIR」列を選択します。Copy Output Fields Add は、3ヶ月の場合は「r3mo_」、6ヶ月の場合は「r6mo_」を入力します。

Expressionには以下の通り設定しました。3ヶ月と6ヶ月分の合計値に対してそれぞれ3または6で割ることで、平均値を出力します。

• 3ヶ月の平均

[_CurrentField_] / 3

• 6ヶ月の平均

[_CurrentField_] / 6

ここまでで、3つのデータの準備ができました。

• 1つ目：Record IDツールでIDを割り当てたデータ

• 2つ目：3ヶ月の平均を出力したデータ

• 3つ目：6ヶ月の平均を出力したデータ

準備した3つのデータを、Join -> Join Multiple を使用して結合します。Join by Specific Fields で「RecordID」列を指定して結合し、あわせて不要な列を削除します。

これで完成です。

補足

ゴールとして用意されている解答と今回作成した解答を比べてみると、一部数値が合致しませんでした。模範解答として提供されているワークフローを確認すると、Multi-Row Formulaツールにおいて、3ヶ月の平均を出力する場合はGroup By (Optional) に「HP Category」が選択されていますが、6ヶ月の平均を出力する場合は「HP Category」が選択されていません。模範解答のワークフローを修正して出力した結果と今回作成した解答でデータが合致することを確認しました。

最後に

今回はWeekly ChallengeのChallenge #3: Running Averagesに挑戦してみました。コミュニティには様々なユーザーの解答が投稿されていますので、自身が作成したワークフローと比べてみるのも楽しいかと思います。これからもWeekly Challengeに挑戦していきます。